﻿using System;
using System.Collections.Generic;
using System.Security.Cryptography;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Web.Configuration;
using System.Data.SqlClient;
using System.Windows.Forms;

public class DataAccess
{
    public static string __DefaultConnectionString = WebConfigurationManager.ConnectionStrings["DB_BookStoreConnectionString"].ToString();

    public static SqlConnection GetConnection()
    {
        return GetConnection(__DefaultConnectionString);
    }

    public static SqlConnection GetConnection(string strConn)
    {
        SqlConnection conn = new SqlConnection(strConn);
        if (conn.State == ConnectionState.Closed)
            conn.Open();
        return conn;
    }

    public static string GetEncryptedPassword(string password)
    {
        MD5CryptoServiceProvider md5Hasher = new MD5CryptoServiceProvider();
        byte[] hashedBytes;
        UTF8Encoding encoder = new System.Text.UTF8Encoding();
        hashedBytes = md5Hasher.ComputeHash(encoder.GetBytes(password));
        return BitConverter.ToString(hashedBytes);
    }

    public static DataRow GetDataRow(DataTable dt, string colName, object value)
    {
        foreach (DataRow row in dt.Rows)
        {
            if(row[colName].Equals(value))
                return row;
        }
        return null;
    }

    #region Execute query without parameters
    public static DataTable Execute(string name, CommandType type)
    {
        // data table
        DataTable dt = new DataTable();
        // connection
        SqlConnection conn = GetConnection();
        // command
        SqlCommand cmd = new SqlCommand();
        cmd.CommandType = type;
        cmd.CommandText = name;
        cmd.Connection = conn;
        // adapter
        SqlDataAdapter da = new SqlDataAdapter();
        da.SelectCommand = cmd;
        try
        {
            da.Fill(dt);
        }
        catch(Exception ex)
        {
            MessageBox.Show(ex.ToString());
        }
        return dt;
    }
    // Select data from a table using stored procedure
    public static DataTable ExecuteProcedure(string storedProcedureName)
    {
        return Execute(storedProcedureName, CommandType.StoredProcedure);
    }
    // Select data from a table using query string
    public static DataTable ExecuteQuery(string queryName)
    {
        return Execute(queryName, CommandType.Text);
    }
    #endregion

    #region Execute query with parameters
    public static DataTable Execute(string name, SqlParameter[] _params, CommandType type)
    {
        // data table
        DataTable dt = new DataTable();
        // connection
        SqlConnection conn = GetConnection();
        // command
        SqlCommand cmd = new SqlCommand();
        cmd.CommandType = type;
        cmd.CommandText = name;
        cmd.Connection = conn;
        cmd.Parameters.AddRange(_params);
        // adapter
        SqlDataAdapter da = new SqlDataAdapter();
        da.SelectCommand = cmd;
        try
        {
            da.Fill(dt);
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.ToString());
        }
        return dt;
    }
    // Execute query with parameters using query string
    public static DataTable ExecuteQuery(string queryName, params SqlParameter[] _params)
    {
        return Execute(queryName, CommandType.Text);
    }
    // Execute query with parameters using stored procedure
    public static DataTable ExecuteProcedure(string storedProcedureName, params SqlParameter[] _params)
    {
        return Execute(storedProcedureName, _params, CommandType.StoredProcedure);
    }

    #endregion

    #region Execute non-query without parameters
    public static int ExecuteNonQuery(string name, CommandType type)
    {
        SqlConnection conn = GetConnection();
        SqlCommand cmd = new SqlCommand();
        cmd.CommandType = type;
        cmd.CommandText = name;
        cmd.Connection = conn;
        int count = 0;
        try
        {
            count = cmd.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.ToString());
        }
        
        return count;
    }
    // Execute non-query without parameters using query string
    public static int ExecuteNonQuery(string queryName)
    {
        return ExecuteNonQuery(queryName, CommandType.Text);
    }
    // Execute non-query without parameters using stored procedure
    public static int ExecuteProcNonQuery(string storedProcedureName)
    {
        return ExecuteNonQuery(storedProcedureName, CommandType.StoredProcedure);
    }
    #endregion

    #region Execute non-query with parameters
    public static int ExecuteNonQuery(string name, SqlParameter[] _params, CommandType type)
    {
        SqlConnection conn = GetConnection();
        SqlCommand cmd = new SqlCommand();
        cmd.CommandType = type;
        cmd.CommandText = name;
        cmd.Connection = conn;
        cmd.Parameters.AddRange(_params);
        int count = 0;
        try
        {
            count = cmd.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.ToString());
        }

        return count;
    }
    // Execute non-query with parameters using query string
    public static int ExecuteNonQuery(string queryName, params SqlParameter[] _params)
    {
        return ExecuteNonQuery(queryName, _params, CommandType.Text);
    }
    // Execute non-query with parameters using stored procedure
    public static int ExecuteProcNonQuery(string storedProcedureName, params SqlParameter[] _params)
    {
        return ExecuteNonQuery(storedProcedureName, _params, CommandType.StoredProcedure);
    }
    #endregion

    #region Play video
    public static string PlayVideoYoutube(string sID, int iWith, int iHeight)
    {
        string strHTML = "";
        strHTML += "<iframe title='YouTube video player'";
        strHTML += " width='" + iWith.ToString() + "' height='" + iHeight.ToString() + "'";
        strHTML += " src='http://www.youtube.com/embed/" + sID + "' ";
        strHTML += " frameborder='0' allowfullscreen></iframe>";
        return strHTML;
    }
    #endregion
}

